
package com.hjm.common.utils.excel;

import com.hjm.common.utils.DateUtils;
import com.hjm.common.utils.LogUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.ArrayList;
import java.util.List;


/**
 * @Author Gdd
 * @Description //写入excel
 * @Param
 * @return
 **/
public class OutbbExcel {


	/*
	 * 标题样式
	 */
	private static void createCell(Workbook wb,Row row,int i,HorizontalAlignment halign,VerticalAlignment valign,String titleTextName){
		         Cell cell=row.createCell(i);  // 创建单元格
		         cell.setCellValue(titleTextName);  // 设置标题
		         CellStyle cellStyle=wb.createCellStyle(); // 创建单元格样式
		         cellStyle.setAlignment(halign);  // 设置单元格水平方向对其方式
		         cellStyle.setVerticalAlignment(valign); // 设置单元格垂直方向对其方式
		         cell.setCellStyle(cellStyle); // 设置单元格样式
	}

	/*
	 * 内容样式
	 */
	private static void createText(CellStyle cellStyle,Row row,int i,short halign,short valign,String textContext){
		//第二列收个
		//从第二列第二个开始循环
        Cell cell=row.createCell(i);  // 创建单元格
        cell.setCellValue(textContext);  // 写入内容
        cellStyle.setAlignment(halign);  // 设置单元格水平方向对其方式
        cellStyle.setVerticalAlignment(valign); // 设置单元格垂直方向对其方式
        cell.setCellStyle(cellStyle); // 设置单元格样式
    }

	/**
	 * ********************************
	 * 方法:readExcel
	 * 参数:{}
	 * 描述:（创建一个excel并且添加表头 和sheet）
	 * 作者:Gd
	 * 返回:int
	 * ********************************
	 */
	public static void creteExcel( String filePath,String fileName,String excelSheetName,String [] tibleName,int dataLength) throws Exception{
		if (fileName.endsWith(".xls")) {
			fileName = fileName+"x";
		}
		LogUtils.logInfo("开始创建文件目录:"+filePath);
		File file = new File(filePath);
		if (!file.exists()) {
			file.mkdirs();
		}
		LogUtils.logInfo("开始创建文件:"+filePath+fileName);
		File files = new File(filePath+fileName);
		if (!files.exists()) {
			files.createNewFile();
		}

		XSSFWorkbook wb = new XSSFWorkbook(); //创建工作博
		Sheet  createSheet = wb.createSheet(excelSheetName);
		CellStyle createCellStyle = wb.createCellStyle();
		Row row = createSheet.createRow(0); //创建一行
		row.setHeightInPoints(15); // 行高
		createSheet.setDefaultColumnWidth(17);

		//合并特定格式单元格
		CellRangeAddress CellRangeAddress = new CellRangeAddress(1,dataLength,0,0);
		createSheet.addMergedRegion(CellRangeAddress);
		CellRangeAddress CellRangeAddress1 = new CellRangeAddress(1,dataLength,12,12);
		createSheet.addMergedRegion(CellRangeAddress1);
		CellRangeAddress CellRangeAddress2 = new CellRangeAddress(1,dataLength,13,13);
		createSheet.addMergedRegion(CellRangeAddress2);

		//RegionUtil.setBorderTop(HSSFBorderFormatting.BORDER_THICK, CellRangeAddress, createSheet, wb);
		//RegionUtil.setBorderTop(HSSFBorderFormatting.BORDER_THICK, CellRangeAddress1, createSheet, wb);

		//往创建好的excel写入表头
		//根据标题循环创建表格（行）
		for (int i = 0; i < tibleName.length; i++) {
			createCell(wb, row, (short)0+i, HorizontalAlignment.CENTER, VerticalAlignment.CENTER,tibleName[i]);
		}

		FileOutputStream fileOut = null;
		try {
			fileOut = new FileOutputStream(files);
			wb.write(fileOut);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				if (fileOut != null) {
					fileOut.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}


	}





	/**
	 * ********************************
	 * 方法:outExcel
	 * 参数:{filePath 文件路径}
	 * 参数:{fileName 文件名称}
	 * 参数:{excelSheetName excelSheet 名称}
	 * 参数:{tibleName 表名称}
	 * 参数:{data 要写入的数据}
	 * 参数:{isDel 是否删除原模板重新创建}
	 * 描述:（可基于excel数据模版  往后面追加数据 数据最大支持 1048576）
	 * 描述:（创建模版暂时只支持 .xlsx格式）
	 * 作者:Gd
	 * 返回:void
	 * *
	 * @throws Exception *******************************
	 */
	public static void outExcel(String filePath,String fileName,String excelSheetName,String[] tibleName,List<String[]> data) throws Exception{
		LogUtils.logInfo("开始写入文件:"+filePath+fileName);

		//文件存在 不创建 直接 根据文件当中的已有数据往下追加
		//反之创建一个带表头的excel文件
		//如果是 .xls格式 默认修改文件后缀为.xlsx
		if (fileName.endsWith(".xls")) {
			fileName = fileName + "x";
		}
		File file = new File(filePath + fileName);
		//创建一个带表头的excel
		if (!file.exists()) {
			creteExcel(filePath,fileName, excelSheetName,tibleName,data.size());
		}

		//读取创建好的excel
 		FileInputStream fis = new FileInputStream(file);

		XSSFWorkbook ps = new XSSFWorkbook(fis);

		SXSSFWorkbook wb = new SXSSFWorkbook(ps, 100);

		CellStyle cellStyle = wb.createCellStyle();

		Sheet sheetAt = ps.getSheetAt(0); // 获取第一个sheet

		//合并单元格
		//setHb(sheetAt);

		int lastRowNum = sheetAt.getLastRowNum()+1; //最后一个行号

		//写入数据
		for (int i = 0; i < data.size(); i++) {
			Row createRow = sheetAt.createRow(lastRowNum+i);
			String[] value = data.get(i);
			for (int j = 0; j < value.length; j++) {
				createText(cellStyle, createRow, j, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_BOTTOM, value[j]);
			}
		}

		FileOutputStream fileOut = null;
		try {
			fileOut = new FileOutputStream(file);
			wb.write(fileOut);
			LogUtils.logInfo("写入完成:"+file);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			LogUtils.logInfo("创建excel异常......." + e);
		} catch (IOException e) {
			e.printStackTrace();
			LogUtils.logInfo("写入excel异常......." + e);
		} finally {
			try {
				if (wb != null) {
					wb.dispose();
					wb.close();
				}
				if (fileOut != null) {
					fileOut.close();
				}
				if (ps != null) {
					ps.close();
				}
				if (fis != null) {
					fis.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
				LogUtils.logInfo("excel写入连接关闭异常......." + e);
			}
		}
	}

	//excel导出响应页面请求
	public static void setResponseHeaderWeb(HttpServletResponse response, String fileName,Workbook workbook) {
		OutputStream os = null;
		try {
			fileName = new String(fileName.getBytes(),"ISO8859-1");
			response.setContentType("application/octet-stream;charset=ISO8859-1");
			response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
			response.addHeader("Pargam", "no-cache");
			response.addHeader("Cache-Control", "no-cache");
			os = response.getOutputStream();
			workbook.write(os);
			os.flush();
		} catch (Exception ex) {
			ex.printStackTrace();
		}finally {
			if (os != null) {
				try {
					os.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}

		}
	}






	public static void main(String[] args) throws Exception {
		//String path = "D:\\贷记调整2.xls";
		String path = "e:/home/web/webtomcat/rfexcel/";
		String fileName = "钱宝终端汇总-" + DateUtils.getSimpleDateTimeMillisecond() + ".xlsx";

		String[] tibleName = {"交易日期","机构简称","交易金额","总笔数","编号"};
		List<String[]> list = new ArrayList<String[]>();
		String [] data = new String [4];
		for (int i = 0; i < 10; i++) {
			data[0] = "2022-08-1"+i;
			data[1] = "测试"+i;
			data[2] = "￥365230.00";
			data[3] = "365230";
			data[3] = "YUITJGHNBMKFHIG"+i;
			list.add(data);
			data = new String [5];
			System.out.println(i);
		}

		String excelSheetName = DateUtils.dateTime();
		outExcel(path,fileName,excelSheetName, tibleName, list);
//		long start = System.currentTimeMillis();
//		outExcel(path,excelSheetName,tibleName,list);
//		long end = System.currentTimeMillis();
//		System.out.println((end-start)/1000+"秒");
	}



}

	